package com.moarea.app.util;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@SuppressWarnings("deprecation")
public class CreateXlsFormatDocument {
	
	private static Log log = LogFactory.getLog(CreateXlsFormatDocument.class);
	
	private static final String[] readomSequence=new String[]{"A","B","C","D"
                                                         ,"E","F","G","H"
                                                         ,"I","J","K","L"
                                                         ,"M","N","O","P"
                                                         ,"Q","R","S","T"
                                                         ,"U","V","W","X"
                                                         ,"Y","Z"};
	
	

	AnyFormatDocProp prop;

	private List<Map<String, Object>> dataList;

	public static final int LENGTH = 500;

	public static final short TYPE_DEFAULT = -1;
	private Workbook workbook;
	
	private int beginDataRow=0;  //数据开始行

	/**
	 * 输出workbook
	 * 
	 * @return
	 */
	public Workbook getWbout() {
		return workbook;
	}

	/**
	 * 输出workbook
	 * 
	 */
	public void setWbout(Workbook workbook) {
		this.workbook = workbook;
	}

	public CreateXlsFormatDocument(AnyFormatDocProp prop,
			List<Map<String, Object>> list) {
		this.prop = prop;
		this.dataList = list;
	}

	/**
	 * 建表（简单模式）
	 */
	public String createContentSimple() {
		StringBuffer bf = new StringBuffer();
		// .........................................................标题
		if(null != prop.getContxt_title()){
			bf.append(prop.getContxt_title() + "\n");
		}
		// .........................................................表头
		if(null == prop.getContxt_head()) return bf.toString();
		bf.append(prop.getContxt_head() + "\n");
		// .........................................................表内容
		String[] heads = prop.getContxt_head().split(",");
		for (Map<String, ?> data_row : dataList) {
			for (String str : heads) {
				char end = bf.charAt(bf.length() - 1);
				if (end != '\n')
					bf.append(",");
				bf.append(String.valueOf(data_row.get(str)));
			}
			bf.append("\n");
		}
		// .........................................................表合计
		Map<String, BigDecimal> digitals = getTotalDataMap();
		if (digitals.size() != 0) {
			bf.append("合计:");
			for (int i = 1; i < heads.length; i++) {
				if (digitals.get(heads[i]) == null)
					bf.append(",");
				else
					bf.append("," + digitals.get(heads[i]));
			}
		}
		return bf.toString();
	}

	/**
	 * 建表
	 */
	public void createContent()throws  Exception {
		String nullStr = null;
		workbook = (workbook == null ? new HSSFWorkbook() : workbook);

//		CreationHelper createHelper = workbook.getCreationHelper();
		Sheet sheet=null;
		if (StringUtils.isNotEmpty(prop.getContxt_SubTitle()))
			sheet = workbook.createSheet(prop.getContxt_SubTitle());
		else
		 sheet = workbook.createSheet("new sheet");
		String[] heads=null;
		String[] datahead=null;
		if (StringUtils.isNotEmpty( prop.getContxt_head())){
		   heads =   prop.getContxt_head().split(",");
		  datahead=heads;
		}
		else
		{
			//heads.length=prop.getColumnContxt().length;
			
			//List<String> he=new ArrayList<String>();
			//List<String> he1=new ArrayList<String>();
			heads=new String[prop.getColumnContxt().length];
			datahead=new String[prop.getColumnContxt().length];
			for (int i=0;i<prop.getColumnContxt().length;i++)
			{
				heads[i]=prop.getColumnContxt()[i][0];
				datahead[i]=prop.getColumnContxt()[i][1];
				
			}
			prop.setContxt_head(heads);
		}
		
		if (heads.length<5)
			throw new Exception("列必须大于等于5");
			// new CreateXlsFormatDocument(prop, list);
		
		
		
		short vIndex = 0;
		Font title_font = workbook.createFont();
		title_font.setBoldweight(Font.BOLDWEIGHT_BOLD);
		title_font.setFontHeightInPoints((short)15);
		Font head_font = workbook.createFont();
		head_font.setBoldweight(Font.BOLDWEIGHT_BOLD);
		head_font.setFontName("宋体");
		DataFormat format = workbook.createDataFormat();
		CellStyle title_sytle = getStrDataCellStyle(null);
		title_sytle.setFont(title_font);
		title_sytle.setWrapText(true);
		CellStyle head_style = getStrDataCellStyle(null);
		head_style.setFont(head_font);
		Row row = null;
		// .........................................................标题
		setAlignCenter(title_sytle);
		if (null != prop.getContxt_title() && null != prop.getContxt_head()) {
			row = sheet.createRow(vIndex++);
			row.setHeight((short)(sheet.getDefaultRowHeight() + 150));
			mergeCells((short)0, (short)(heads.length -1), sheet, row,title_sytle, nullStr);
			String ps = prop.getContxt_title();
			RichTextString ts= new HSSFRichTextString(ps);
			if(prop.getCreate_date()!=null){
				ps += ("\n" + "日期:" + prop.getCreate_date());
				ts= new HSSFRichTextString(ps);
				ts.applyFont(0,prop.getContxt_title().length(),title_font);
				ts.applyFont(prop.getContxt_title().length()+ 1,ps.length(),head_font);
				row.setHeight((short)(2*sheet.getDefaultRowHeight() + 150));
			}
			row.getCell(0).setCellValue(ts);
		}
		/**
		 * 副标题处理
		 */
		if (StringUtils.isNotEmpty(prop.getContxt_SubTitle())){
			row = sheet.createRow(vIndex++);
			setAlignCenter(title_sytle);
			mergeCells((short)0, (short)(heads.length -1), sheet, row,title_sytle, nullStr);
			RichTextString ts= new HSSFRichTextString(prop.getContxt_SubTitle());
			row.getCell(0).setCellValue(ts);	
			operateRowBorder(row,2,2,-1);
		}
		/**单位**/
		row = sheet.createRow(vIndex++);
		if(prop.getOrgNameTitle()==null)
		{
		quickWriteCell("单位：",row,head_style);
		}
		else
		{
			quickWriteCell(prop.getOrgNameTitle(),row,head_style);	
		}
		
		if (StringUtils.isNotEmpty(prop.getContxt_SubTitle())){
	    	
	    	quickWriteCell(prop.getOrgName(),row,head_style);
		}
		//列印日期
		SimpleDateFormat formatter;
		
		quickWriteCell("列印日期：",row,head_style,(short)(heads.length-2));
		formatter=new SimpleDateFormat("yyyy-MM-dd");
		
		quickWriteCell(formatter.format(new Date()),row,head_style,(short)(heads.length-1));
		operateRowBorder(row,2,2,-1);
		/**列印范围**/
		
		row = sheet.createRow(vIndex++);
		
		//row.getCell(0).setCellValue(new HSSFRichTextString("列印范围："));
		if(prop.getRangeTitle()==null)
		{
		quickWriteCell("列印范围：",row,head_style);
		}
		else
		{
			quickWriteCell(prop.getRangeTitle(),row,head_style);	
			
		}
		if (StringUtils.isNotEmpty(prop.getRange())){
	    	//row.getCell(1).setCellValue(new HSSFRichTextString(prop.getRange()));
			if (heads.length>3){
			 //mergeCells((short)2, (short)3, sheet, row,title_sytle, prop.getRange());
			quickWriteCell(null,row,head_style,(short)2);
			mergeCells((short)1, (short)2, sheet, row,head_style, prop.getRange());
			}
			else
			
	    	quickWriteCell(prop.getRange(),row,head_style);
		}
		//row.getCell((short)(heads.length -2)).setCellValue(new HSSFRichTextString("列印时间："));
		quickWriteCell("列印时间：",row,head_style,(short)(heads.length-2));
		formatter=new SimpleDateFormat("HH:mm:ss");
		//row.getCell((short)(heads.length -1)).setCellValue(new HSSFRichTextString(formatter.format(new Date())));
		quickWriteCell(formatter.format(new Date()),row,head_style,(short)(heads.length-1));
		operateRowBorder(row,2,2,-1);
		
		
		
		
		
		
		// .........................................................标题下方补充内容
		if(null != prop.getContxt_titleAttach()){
			operateRowBorder(row, -1, 0,-1);
			for (String[] strs : prop.getContxt_titleAttach()) {
				CellStyle newStyle = getStrDataCellStyle(strs[1]);
				newStyle.setFont(head_font);
				row = sheet.createRow(vIndex++);
				mergeCells((short)0, (short)(heads.length -1), sheet, row,newStyle, strs[0]);
				operateRowBorder(row, 2, 0,-1);
			}
			
		}
		// .........................................................表头
		
		if (null !=prop.getFirstDataColumn())
		{
			row = sheet.createRow(vIndex++);
			int begin=0;
			int end=0;
			//modify by sxjun start 2012-5-30 
			boolean flag = false;//add
			int beglog=0;//add
			for (int ii=0 ;ii< prop.getFirstDataColumn().length;ii++) {
				
				String str=prop.getFirstDataColumn()[ii].replaceAll(" ", "");
				CellStyle style = getStrDataCellStyle(null);
				style.setFont(head_font);
				quickWriteCell(str, row,style);
				
				if (StringUtils.isEmpty(str)) {
					begin=ii;
					if(flag){//add
						flag=false;
						beglog = ii;
					}
				}
				else
				{
					if(flag) beglog += 1;//add
					end=ii;
					flag=true;//add
				}
				
				if (StringUtils.isNotEmpty(str)&& begin<end )
				{
					//合并单元格
					//CellStyle style1 = getStrDataCellStyle(null);
					//style1.setFont(head_font);
					mergeCells((short)(beglog),(short)(end),sheet, row,style, str);//原begin-1 => beglog
				}				
			}
			//modify by sxjun end 2012-5-30 
			
			
		}
		
		
		
		if (null != prop.getContxt_head()){
			row = sheet.createRow(vIndex++);
			
			
			
			for (String str : heads) {
				
				CellStyle style = getStrDataCellStyle(null);
				style.setFont(head_font);
				quickWriteCell(str, row,style);
			}
			
			//
			if (null !=prop.getFirstDataColumn())
			{
				Row fristhead=	 sheet.getRow(row.getRowNum()-1);
				
				
				for(int jj=0;jj<heads.length;jj++){
					
					 if ( row.getCell(jj).getStringCellValue().equals(fristhead.getCell(jj).getStringCellValue()))
					 {
							 sheet.addMergedRegion(new CellRangeAddress(fristhead.getRowNum(), row.getRowNum() , jj,jj));
						 //sheet.set
					 }
					
					
				}
				
				
				//fristhead.get
			}
			
			
			
		// .........................................................表内容
			beginDataRow=vIndex; //数据开始行
			
		
	    
			for (Map<String, ?> data_row : dataList) {
				row = sheet.createRow(vIndex++);
				
				
				
				
				
				for (String str : datahead) {
					if(data_row.get(str) == null){
						quickWriteCell(null,row,getStrDataCellStyle(null));
					}
					else{
						
						//Integer
						String cname= data_row.get(str).getClass().getSimpleName();
					
						
						if ("BigDecimal".equals(cname)){
							quickWriteCell((BigDecimal) data_row.get(str), row,format);
						}
						else if  ("Integer".equals(cname)){
							quickWriteCell((Integer) data_row.get(str), row,format);
						}	
						else if ("Timestamp".endsWith(cname))
						{
							
							String date= DateUtil.dateToString((Date)data_row.get(str),"yyyy-MM-dd");
							quickWriteCell(date, row,getStrDataCellStyle("left"));
						}   
						else{
							quickWriteCell(String.valueOf(data_row.get(str)), row,getStrDataCellStyle("left"));
						}
					}
	
				}
	
			}			
			
			
			//加入合计行写入公式
			
			if (prop.getContxt_total()!=null && prop.getContxt_total().length>0){
				row = sheet.createRow(vIndex++);
				setTotalRowFormula(row,vIndex-1,heads,sheet);
			}
			
			
			
			
			
			
			
			

		
		}
		// .........................................................表格下方内容
		if (null != prop.getContxt_endAttach()){
			for (String[] strs : prop.getContxt_endAttach()) {
				CellStyle newStyle = getStrDataCellStyle(strs[1]);
				row = sheet.createRow(vIndex++);
				mergeCells((short)0, (short)(heads.length -1), sheet, row,newStyle, strs[0]);
				operateRowBorder(row, 2, 0,-1);
			}
			operateRowBorder(row,-1,0,1);
			
		}
		//表格列宽设置
		
		for (int i = 0; i < heads.length; i++) {
			if(prop.getColumnWidth() != null)
				sheet.setColumnWidth(i, prop.getColumnWidth()[i]);
			else
				sheet.setColumnWidth(i,3000);    
				//sheet.autoSizeColumn(i); 
		}
	}

	/**
	 * 公式形势设置合计行
	 * @param row
	 */
	private void setTotalRowFormula(Row row, int enddatarowno, String[] heads, Sheet sheet){
		
		int col=0;
		for(int i=0;i<prop.getContxt_total().length;i++){			
			for(int j=0;j<heads.length;j++){
				if (prop.getContxt_total()[i].equals(heads[j]))
				{
					if (col==0)
					{
						col=j;
					}
					String bingcell=readomSequence[j]+(beginDataRow+1);
					String endcell=readomSequence[j]+(enddatarowno);
					
					String formula="SUM("+bingcell +":"+endcell+")";
					
					//SUM(H6:H15)
					
					quickWriteCellFormula(formula,row,(short)j);
					
				}
				
				
			}			
		}
		
		
		CellStyle style = getDecDataCellStyle();
		setAlignCenter(style);
		mergeCells((short)0, (short)(col-1), sheet, row,style, "合计：");
		
		
	}
	
	
	
	private Map<String, BigDecimal> getTotalDataMap() {
		Map<String, BigDecimal> digitals = new HashMap<String, BigDecimal>();
		if (null == prop.getContxt_total()) {
			return digitals;
		}
		String[] heads = prop.getContxt_head().split(",");
		String[] totals = prop.getContxt_total();
		BigDecimal[] temps = new BigDecimal[totals.length];
		for (int i = 0; i < temps.length; i++) {
			temps[i] = new BigDecimal(0);
		}
		for (Map<String, ?> data_row : dataList) {
			for (int i = 0; i < totals.length; i++) {
				for (String str : heads) {
					if (str.equals(totals[i])) {
						if (data_row.get(str)!=null && "BigDecimal".equals(data_row.get(str).getClass()
								.getSimpleName()))
							temps[i] = temps[i].add((BigDecimal) data_row
									.get(str));
					}
				}
			}
		}
		for (int i = 0; i < temps.length; i++) {
			digitals.put(totals[i], temps[i]);
		}
		return digitals;

	}
	/**
	 * 得到String型数据的表格书写格式
	 * @return
	 */
	private CellStyle getStrDataCellStyle(String align){
		CellStyle style = workbook.createCellStyle();
		//addBorders(style);
		setAlignCenter(style);
		if("left".equals(align))
			setAlignLeft(style);
		if("right".equals(align))
			setAlignRight(style);
		return style;
	}
	/**
	 * 得到Decimal型数据的表格书写格式
	 * @return
	 */
	private CellStyle getDecDataCellStyle(){
		CellStyle style = getStrDataCellStyle(null);
		setAlignRight(style);
		return style;
	}
	
	/**
	 * 在单元格内写入数据
	 * @param str	String型数据
	 * @param row
	 * @param style
	 */
	private void quickWriteCell(String str, Row row, CellStyle style) {
		Cell cell = row.createCell(row.getLastCellNum() == -1 ? 0 : row
				.getLastCellNum());
		cell.setCellStyle(style);
		if(str != null)
		cell.setCellValue(str);
		//cell.setCellFormula(arg0)
	}
	
	private void quickWriteCell(String str, Row row, CellStyle style, short coun) {
		
		short i=row.getLastCellNum() == -1 ? 0 : row.getLastCellNum();
		
		Cell cell = row.createCell(i);
		cell.setCellStyle(style);
		if( i==coun )
		cell.setCellValue(str==null?"":str);
		else
		 quickWriteCell(str, row, style, coun);
	}
	
	
	private void quickWriteCellFormula(String formula, Row row, short coun) {
		
		short i=row.getLastCellNum() == -1 ? 0 : row.getLastCellNum();
		
		Cell cell = row.createCell(i);
		CellStyle style = getDecDataCellStyle();
		cell.setCellStyle(style);
		if( i==coun ){
			if (StringUtils.isNotEmpty(formula))
			{
				cell.setCellFormula(formula);
		   // cell.setCellValue(formula==null?"":formula);
			}
		}
		else
			quickWriteCellFormula(formula, row,  coun);
	}
	
	
	
	
	
	
	
	
	
	
	/**
	 * 在单元格内写入数据
	 * @param dec
	 * @param row
	 * @param format
	 */
	private void quickWriteCell(BigDecimal dec, Row row, DataFormat format) {
		Cell cell = row.createCell(row.getLastCellNum() == -1 ? 0 : row
				.getLastCellNum());
		CellStyle style = getDecDataCellStyle();
		style.setDataFormat(format.getFormat("#,##0.00"));
		cell.setCellStyle(style);
		if(dec != null)
		cell.setCellValue(Double.valueOf(dec.toString()));
	}
	
	private void quickWriteCell(int dec, Row row, DataFormat format) {
		Cell cell = row.createCell(row.getLastCellNum() == -1 ? 0 : row.getLastCellNum());
		CellStyle style = getDecDataCellStyle();
		style.setDataFormat(format.getFormat("#,##0"));
		cell.setCellStyle(style);
		if(dec != 0)
		cell.setCellValue(dec);
		
	}
	


	/**
	 *  定位写入数据
	 * @param dec
	 * @param index
	 * @param row
	 * @param format
	 */
	private void quickWriteCell(BigDecimal dec, short index, Row row, DataFormat format) {
		Cell cell = row.createCell(index);
		CellStyle style = getStrDataCellStyle(null);
		setAlignRight(style);
		if(null != format)
			style.setDataFormat(format.getFormat("#,##0.00"));
		cell.setCellStyle(style);
		cell.setCellValue(Double.valueOf(dec.toString()));
	}

	/**
	 * 加边框
	 * @param style
	 */
	private void addBorders(CellStyle style) {
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setBorderTop(CellStyle.BORDER_THIN);
	}
	/**
	 * 格式居中
	 * @param style
	 */
	private void setAlignCenter(CellStyle style){
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	}
	/**
	 * 格式靠右
	 * @param style
	 */
	private void setAlignRight(CellStyle style){
		style.setAlignment(CellStyle.ALIGN_RIGHT);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	}
	/**
	 * 格式靠左
	 * @param style
	 */
	private void setAlignLeft(CellStyle style){
		style.setAlignment(CellStyle.ALIGN_LEFT);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	}
	/**
	 * 合并单元格(横向)
	 * @param start
	 * @param end
	 * @param sheet
	 * @param row
	 */
	@SuppressWarnings("deprecation")
	private void mergeCells(short start, short end, Sheet sheet, Row row, CellStyle style, String value){
		for(short i = start;i <= end; i ++){
			Cell cell = row.createCell(i);
			cell.setCellStyle(style);
		}
		sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), (short) row.getRowNum(), start,end));
		if(null != value)
			//row.getCell(arg0)
			row.getCell(start).setCellValue(value);
	}
	/**
	 * 删除row行边框
	 * @param row
	 * @param mode1 0:不改,1:上边,-1:下边,2:上下边
	 * @param mode2 0:不改,1:左边,-1:右边,2:左右边
	 * @param action 1:加,-1:删
	 */
	private void operateRowBorder(Row row, int mode1, int mode2, int action){
		short value;
		if(action>0)
			value = CellStyle.BORDER_THIN;
		else
			value = CellStyle.BORDER_NONE;
			
		for (Cell cell : row) {
			CellStyle style = cell.getCellStyle();
			operateCellBorderStyle(style,mode1,1,value);
			operateCellBorderStyle(style,mode2,-1,value);
		}
	}
	/**
	 * 设置style（用于操作边框）
	 * @param style
	 * @param mode1	0:不改,1:上或左边,-1:下或右边,2:上下或左右边
	 * @param mode2 1:上下模式,-1:左右模式
	 * @param value 边框值
	 */
	private void operateCellBorderStyle(CellStyle style, int mode1, int mode2, short value){
		switch (mode1) {
			case 0:
				break;
			case 1:
				if (mode2 > 0)
					style.setBorderTop(value);
				else
					style.setBorderLeft(value);
				break;
			case -1:
				if (mode2 > 0)
					style.setBorderBottom(value);
				else
					style.setBorderRight(value);
				break;
			case 2:
				if (mode2 > 0) {
					style.setBorderTop(value);
					style.setBorderBottom(value);
				} else {
					style.setBorderLeft(value);
					style.setBorderRight(value);
				}
				break;
			default:
				break;
		}
	}

}
